using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
using WPFExcelReport.Helpers;
using WPFExcelReport.Controls;
namespace WPFExcelReport
{
    public partial class VQSZNKOPF
    {
        public DataRow Convert2QSZNKOPF(DataRow dwInput, string ITXDBVERSION, bool IsConvert = false)
        {
            DataSet SResult = new DataSet();
            SResult = QueryQSZNKOPF(dwInput, 0);
            DataRow dwReturn = null;
            if (SResult.Tables[0].Rows.Count > 0)
            {
                dwReturn = SResult.Tables[0].Rows[0];
            }
            else
            {
                dwReturn = SResult.Tables[0].NewRow();
            }
            switch (ITXDBVERSION)
            {
                case "YGR":
                    #region=========
         dwReturn["QSKMANDANT"] = " ".PadRight(2 ); 
         dwReturn["QSKAUFKRS"] = " ".PadRight(2 ); 
         dwReturn["QSKAUFLFD"] = " ".PadRight(6 ); 
         dwReturn["QSKAUFPOS"] = " ".PadRight(3 ); 
         dwReturn["QSKSZENNR"] = " ".PadRight(8 ); 
         dwReturn["QSKAENZL"] = " ".PadRight(0 ); 
         dwReturn["QSKAENB"] = " ".PadRight(8 ); 
         dwReturn["QSKAEND"] = " ".PadRight(0 ); 
         dwReturn["QSKNEUB"] = " ".PadRight(8 ); 
         dwReturn["QSKNEUD"] = " ".PadRight(0 ); 
         dwReturn["QSKBEARB"] = " ".PadRight(1 ); 
         dwReturn["QSKLSG"] = " ".PadRight(0 ); 
         dwReturn["QSKANZZENSUREN"] = " ".PadRight(0 ); 
         dwReturn["QSKANZGUETEKL"] = " ".PadRight(0 ); 
         dwReturn["QSKANZAUFMPOS"] = " ".PadRight(0 ); 
         dwReturn["QSKANZKUNDEN"] = " ".PadRight(0 ); 
         dwReturn["QSKANZSTK"] = " ".PadRight(0 ); 
         dwReturn["QSKANZABS"] = " ".PadRight(0 ); 
         dwReturn["QSKGESLG"] = " ".PadRight(0 ); 
         dwReturn["QSKANZSHD"] = " ".PadRight(2 ); 
         dwReturn["QSKANZPKTFEHL"] = " ".PadRight(0 ); 
         dwReturn["QSKANZLNGFEHL"] = " ".PadRight(0 ); 
         dwReturn["QSKANZNAEHTE"] = " ".PadRight(0 ); 
         dwReturn["QSKANZFEHLPKT"] = " ".PadRight(6 ); 
         dwReturn["QSKPFVERGUET"] = " ".PadRight(0 ); 
         dwReturn["QSKLFVERGUET"] = " ".PadRight(0 ); 
         dwReturn["QSKVERGUETUNG"] = " ".PadRight(0 ); 
         dwReturn["QSKREPKOST"] = " ".PadRight(0 ); 
         dwReturn["QSKFILLER"] = " ".PadRight(104 ); 
                    #endregion
                    break;
                case "JLT":

                    break;
                default:

                    break;
            }
            if (IsConvert)
            {
                foreach (DataColumn cm in dwInput.Table.Columns)
                {
                    if (dwInput[cm.ColumnName].ToString() == "")
                        continue;
                    if (dwReturn.Table.Columns.Contains(cm.ColumnName))
                    {
                        dwReturn[cm.ColumnName] = dwInput[cm.ColumnName];
                    }
                }
            }
            return dwReturn;
        }
        public void MapQSZNKOPF(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }
        public void SaveQSZNKOPF(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            setResult = QueryQSZNKOPF(dwData);
            if (setResult.Tables.Count > 0)
            {
                tbLocal = setResult.Tables[0];
                if (tbLocal.Rows.Count > 0)
                {
                    flgExist = true;
                    dwLocal = tbLocal.Rows[0];
                }
                else
                {
                    dwLocal = tbLocal.NewRow();
                }
            }
            MapQSZNKOPF(ref dwLocal, dwData);

            if (!flgExist)
            {

                InsertQSZNKOPF(dwLocal, ITXDBVERSION);
            }
            UpdateQSZNKOPF(dwLocal);
        }
        public void InsertQSZNKOPF(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            switch (ITXDBVERSION)
            {
                case "YGR":
                    or1.CommandText = "INSERT INTO QSZNKOPF(VAKBMANDANT, VAKBKOLL, VAKBART, VAKBKUNDNR, VAKBFARBE, VAKBKDARTNR, VAKBKDARTBEZ, VAKBKDFARBNR, VAKBANDRUCK, VAKBMEGEW, VAKBMENGEH, VAKBGEWVON, VAKBGEWBIS, VAKBGEWCA, VAKBGEWFKT, VAKBGEWGRP, VAKBMEGFKT, VAKBMEGGRP, VAKBAENDZL, VAKBAENDBEN, VAKBAENDDAT, VAKBNEUBEN, VAKBNEUDAT, VAKBFILLER)"
                        + " VALUES (:VAKBMANDANT, :VAKBKOLL, :VAKBART, :VAKBKUNDNR, :VAKBFARBE, '                         ', '                         ', '                             ', ' ', '   ', '   ', 0, 0, ' ', '000000000000', ' ', '000000000000', ' ', 0, '        ', '000000', '        ', '      ', '                                          ')";
                    or1.Parameters.Add("VAKBMANDANT", OracleType.NVarChar).Value = dwData["VAKBMANDANT"].ToString();
                    or1.Parameters.Add("VAKBKOLL", OracleType.NVarChar).Value = dwData["VAKBKOLL"].ToString();
                    or1.Parameters.Add("VAKBART", OracleType.NVarChar).Value = dwData["VAKBART"].ToString();
                    or1.Parameters.Add("VAKBFARBE", OracleType.NVarChar).Value = dwData["VAKBFARBE"].ToString();
                    or1.Parameters.Add("VAKBKUNDNR", OracleType.NVarChar).Value = dwData["VAKBKUNDNR"].ToString();
                    break;
                //VAFMANDANT VAFKOLL VAFART VAFFARBE VAFBEZ VAFFFARBE

            }
            
            Connect2Proxy(or1);
        }
        public DataSet QueryQSZNKOPF(DataRow dwData, int option = 1)
        {
            DataSet Return;
            DataSet Result = new DataSet();
            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 0:
                    or1.CommandText = "SELECT * FROM QSZNKOPF WHERE  ROWNUM<2 ";
                    break;
              
                case 99:
                    or1.CommandText = "SELECT * FROM QSZNKOPF  WHERE ROWNUM<50";
                    break;
                default:
                    break;
            }
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
                Result.Tables[0].TableName = "QSZNKOPF";
            Return = Result;
            return Return;
        }
        public void DeleteQSZNKOPF(DataRow dwData, int option = 1)
        {

            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "DELETE FROM QSZNKOPF   WHERE  VAKBMANDANT=:VAKBMANDANT AND VAKBKOLL=:VAKBKOLL AND VAKBART =:VAKBART AND VAKBFARBE=:VAKBFARBE AND VAKBKUNDNR=:VAKBKUNDNR";
                     or1.Parameters.Add("VAKBMANDANT", OracleType.NVarChar).Value = dwData["VAKBMANDANT"].ToString();
                    or1.Parameters.Add("VAKBKOLL", OracleType.NVarChar).Value = dwData["VAKBKOLL"].ToString();
                    or1.Parameters.Add("VAKBART", OracleType.NVarChar).Value = dwData["VAKBART"].ToString();
                    or1.Parameters.Add("VAKBFARBE", OracleType.NVarChar).Value = dwData["VAKBFARBE"].ToString();
                    or1.Parameters.Add("VAKBKUNDNR", OracleType.NVarChar).Value = dwData["VAKBKUNDNR"].ToString();
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        } 
      
        public void UpdateQSZNKOPF(DataRow dwData, string ITXDBVERSION = "50.24", int option = 1)
        {
            OracleCommand or1;
            DataSet Result = new DataSet();
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    #region====Command==== 
                    or1.CommandText = "UPDATE QSZNKOPF SET "
                    +"QSKMANDANT= :QSKMANDANT,"
                    +"QSKAUFKRS= :QSKAUFKRS,"
                    +"QSKAUFLFD= :QSKAUFLFD,"
                    +"QSKAUFPOS= :QSKAUFPOS,"
                    +"QSKSZENNR= :QSKSZENNR,"
                    +"QSKAENZL= :QSKAENZL,"
                    +"QSKAENB= :QSKAENB,"
                    +"QSKAEND= :QSKAEND,"
                    +"QSKNEUB= :QSKNEUB,"
                    +"QSKNEUD= :QSKNEUD,"
                    +"QSKBEARB= :QSKBEARB,"
                    +"QSKLSG= :QSKLSG,"
                    +"QSKANZZENSUREN= :QSKANZZENSUREN,"
                    +"QSKANZGUETEKL= :QSKANZGUETEKL,"
                    +"QSKANZAUFMPOS= :QSKANZAUFMPOS,"
                    +"QSKANZKUNDEN= :QSKANZKUNDEN,"
                    +"QSKANZSTK= :QSKANZSTK,"
                    +"QSKANZABS= :QSKANZABS,"
                    +"QSKGESLG= :QSKGESLG,"
                    +"QSKANZSHD= :QSKANZSHD,"
                    +"QSKANZPKTFEHL= :QSKANZPKTFEHL,"
                    +"QSKANZLNGFEHL= :QSKANZLNGFEHL,"
                    +"QSKANZNAEHTE= :QSKANZNAEHTE,"
                    +"QSKANZFEHLPKT= :QSKANZFEHLPKT,"
                    +"QSKPFVERGUET= :QSKPFVERGUET,"
                    +"QSKLFVERGUET= :QSKLFVERGUET,"
                    +"QSKVERGUETUNG= :QSKVERGUETUNG,"
                    +"QSKREPKOST= :QSKREPKOST,"
                    +"QSKFILLER= :QSKFILLER,"
                        + " WHERE  "
                        + "  VAKBMANDANT=:VAKBMANDANT AND VAKBKOLL=:VAKBKOLL AND VAKBART =:VAKBART AND VAKBFARBE=:VAKBFARBE AND VAKBKUNDNR=:VAKBKUNDNR";
                    #endregion

                    #region====Parameters====
          or1.Parameters.Add("QSKMANDANT", OracleType.NVarChar).Value = dwData["QSKMANDANT"].ToString(); 
          or1.Parameters.Add("QSKAUFKRS", OracleType.NVarChar).Value = dwData["QSKAUFKRS"].ToString(); 
          or1.Parameters.Add("QSKAUFLFD", OracleType.NVarChar).Value = dwData["QSKAUFLFD"].ToString(); 
          or1.Parameters.Add("QSKAUFPOS", OracleType.NVarChar).Value = dwData["QSKAUFPOS"].ToString(); 
          or1.Parameters.Add("QSKSZENNR", OracleType.NVarChar).Value = dwData["QSKSZENNR"].ToString(); 
          or1.Parameters.Add("QSKAENZL", OracleType.NVarChar).Value = dwData["QSKAENZL"].ToString(); 
          or1.Parameters.Add("QSKAENB", OracleType.NVarChar).Value = dwData["QSKAENB"].ToString(); 
          or1.Parameters.Add("QSKAEND", OracleType.NVarChar).Value = dwData["QSKAEND"].ToString(); 
          or1.Parameters.Add("QSKNEUB", OracleType.NVarChar).Value = dwData["QSKNEUB"].ToString(); 
          or1.Parameters.Add("QSKNEUD", OracleType.NVarChar).Value = dwData["QSKNEUD"].ToString(); 
          or1.Parameters.Add("QSKBEARB", OracleType.NVarChar).Value = dwData["QSKBEARB"].ToString(); 
          or1.Parameters.Add("QSKLSG", OracleType.NVarChar).Value = dwData["QSKLSG"].ToString(); 
          or1.Parameters.Add("QSKANZZENSUREN", OracleType.NVarChar).Value = dwData["QSKANZZENSUREN"].ToString(); 
          or1.Parameters.Add("QSKANZGUETEKL", OracleType.NVarChar).Value = dwData["QSKANZGUETEKL"].ToString(); 
          or1.Parameters.Add("QSKANZAUFMPOS", OracleType.NVarChar).Value = dwData["QSKANZAUFMPOS"].ToString(); 
          or1.Parameters.Add("QSKANZKUNDEN", OracleType.NVarChar).Value = dwData["QSKANZKUNDEN"].ToString(); 
          or1.Parameters.Add("QSKANZSTK", OracleType.NVarChar).Value = dwData["QSKANZSTK"].ToString(); 
          or1.Parameters.Add("QSKANZABS", OracleType.NVarChar).Value = dwData["QSKANZABS"].ToString(); 
          or1.Parameters.Add("QSKGESLG", OracleType.NVarChar).Value = dwData["QSKGESLG"].ToString(); 
          or1.Parameters.Add("QSKANZSHD", OracleType.NVarChar).Value = dwData["QSKANZSHD"].ToString(); 
          or1.Parameters.Add("QSKANZPKTFEHL", OracleType.NVarChar).Value = dwData["QSKANZPKTFEHL"].ToString(); 
          or1.Parameters.Add("QSKANZLNGFEHL", OracleType.NVarChar).Value = dwData["QSKANZLNGFEHL"].ToString(); 
          or1.Parameters.Add("QSKANZNAEHTE", OracleType.NVarChar).Value = dwData["QSKANZNAEHTE"].ToString(); 
          or1.Parameters.Add("QSKANZFEHLPKT", OracleType.NVarChar).Value = dwData["QSKANZFEHLPKT"].ToString(); 
          or1.Parameters.Add("QSKPFVERGUET", OracleType.NVarChar).Value = dwData["QSKPFVERGUET"].ToString(); 
          or1.Parameters.Add("QSKLFVERGUET", OracleType.NVarChar).Value = dwData["QSKLFVERGUET"].ToString(); 
          or1.Parameters.Add("QSKVERGUETUNG", OracleType.NVarChar).Value = dwData["QSKVERGUETUNG"].ToString(); 
          or1.Parameters.Add("QSKREPKOST", OracleType.NVarChar).Value = dwData["QSKREPKOST"].ToString(); 
          or1.Parameters.Add("QSKFILLER", OracleType.NVarChar).Value = dwData["QSKFILLER"].ToString(); 
                    #endregion
                    break; 
                default:
                    break;
            }


            Connect2Proxy(or1);
        }
    }
}
